﻿using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using MySql.Data.MySqlClient;

namespace DBUtility.Standard
{
    internal class MySqlHelper : DBHelper
    {
        #region 初始化必须
        /// <summary>
        ///初始化，需要传入连接字符串
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <returns></returns>
        public MySqlHelper(string connString) : base(connString)
        {

        }
        #endregion

        public override void CreateConnection()
        {
            dbConnection = new MySqlConnection(connectionString);
        }

        public override DbParameter CreateParam(string paramName, DbType dbType, object value, int size, ParameterDirection direction)
        {
            DbParameter param = new MySqlParameter(paramName, dbType);

            if (value != null)
                param.Value = value;
            if (size > 0)
                param.Size = size;
            param.DbType = dbType;
            param.Direction = direction;

            return param;
        }

        /// <summary>
        /// 执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回int类型</returns>
        public override int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, null, cmdParams);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 在事务中执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回int类型</returns>
        public override int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, trans, cmdParams);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行Transact-SQL语句并返回数据集DataSet
        /// </summary>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回DataSet集合</returns>
        public override DataSet ExecuteQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, null, cmdParams);
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 在事务中执行Transact-SQL语句并返回数据集DataSet
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回DataSet集合</returns>
        public override DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, trans, cmdParams);
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行Transact-SQL语句返回内存中数据的一个表
        /// </summary>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回内存中数据的一个表</returns>
        public override DataTable ExecuteNQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, null, cmdParams);
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds.Tables[0];
                    }
                }
            }
        }

        /// <summary>
        /// 在事务中执行Transact-SQL语句返回内存中数据的一个表
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回内存中数据的一个表</returns>
        public override DataTable ExecuteNQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, trans, cmdParams);
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds.Tables[0];
                    }
                }
            }
        }

        /// <summary>
        /// 执行Transact-SQL语句返回List<List<string>>
        /// </summary>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns></returns>
        public override List<List<string>> ExecuteLQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, null, cmdParams);
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        cmd.Parameters.Clear();
                        using (DataTable dt = new DataTable())
                        {
                            List<List<string>> lls = new List<List<string>>();
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                List<string> ls = new List<string>();
                                for (int j = 0; j < dt.Columns.Count; j++)
                                {
                                    ls.Add(dt.Rows[i][j].ToString());
                                }
                                lls.Add(ls);
                            }
                            return lls;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 在事务中执行Transact-SQL语句返回List<List<string>>
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns></returns>
        public override List<List<string>> ExecuteLQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, trans, cmdParams);
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        cmd.Parameters.Clear();
                        using (DataTable dt = new DataTable())
                        {
                            List<List<string>> lls = new List<List<string>>();
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                List<string> ls = new List<string>();
                                for (int j = 0; j < dt.Columns.Count; j++)
                                {
                                    ls.Add(dt.Rows[i][j].ToString());
                                }
                                lls.Add(ls);
                            }
                            return lls;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询，返回DataReader
        /// </summary>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回DataReader</returns>
        public override DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, null, cmdParams);
                MySqlDataReader sdr;
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return sdr;
            }
        }

        /// <summary>
        /// 在事务中执行执行查询，返回DataReader
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回DataReader</returns>
        public override DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, trans, cmdParams);
                MySqlDataReader sdr;
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return sdr;
            }
        }

        /// <summary>
        /// 执行查询，返回结果集中的第一行第一列，忽略其它行其它列
        /// </summary>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回结果集中的第一行第一列，忽略其它行其它列</returns>
        public override object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, null, cmdParams);
                object o = new object();
                o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return o;
            }
        }

        /// <summary>
        /// 在事务中执行查询，返回结果集中的第一行第一列，忽略其它行其它列
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回结果集中的第一行第一列，忽略其它行其它列</returns>
        public override object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams)
        {
            using (MySqlCommand cmd = new MySqlCommand())
            {
                PrepareCmd(cmd, cmdType, cmdText, trans, cmdParams);
                object o = new object();
                o = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return o;
            }
        }
    }
}
